//
//  QSFMDataBaseTool.m
//  HYDClient
//
//  Created by apple on 16/10/25.
//  Copyright © 2016年 song. All rights reserved.
//

#import "QSFMDataBaseTool.h"
#import <FMDB.h>
#import <objc/runtime.h>

@interface QSFMDataBaseTool()
{
    //数据库对象
    FMDatabase *_database;
}
@end

@implementation QSFMDataBaseTool
/**
 *  初始化
 */
- (instancetype)init
{
    self = [super init];
    if (self)
    {
        _database = [FMDatabase databaseWithPath:[self databasePath]];
    }
    return self;
}

/**
 *  返回数据库的路径
 */
- (NSString *)databasePath
{
    NSString *doucmentPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    
    return [doucmentPath stringByAppendingPathComponent:@"data.db"];
}

/**
 *  创建单例对象
 */
+ (instancetype)databaseManager
{
    
    static QSFMDataBaseTool *manager = nil;
    
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[self alloc] init];
    });
    
    return manager;
}

/**
 *  根据模型创建表
 */
- (BOOL)createTableWithObject:(Class)cls andFormName:(NSString *)formName
{
    NSMutableString *sql = [NSMutableString stringWithFormat:@"create table if not exists %@ (myId integer primary key autoincrement", formName];
    
    // 获取字段名字
    NSArray *properties = [self propertiesFromClass:cls];
    
    for (NSString *property in properties)
    {
        [sql appendFormat:@",%@ str",property];
    }
    
    [sql appendString:@")"];
    
    // 执行sql语句
    return [_database executeUpdate:sql];
    
}

/**
 *  获取数据模型中的属性名
 */
- (NSArray *)propertiesFromClass:(Class)cls
{
    // 用于保存所有的属性名字
    NSMutableArray *array = [NSMutableArray array];
    
    // 属性个数
    unsigned int outCount;
    // 获取所有的属性    copy,retain,create都需要手动释放
    objc_property_t *properties = class_copyPropertyList(cls, &outCount);
    
    for (int i = 0; i < outCount; i++)
    {
        // 每个属性的结构体
        objc_property_t property = properties[i];
        // 获取属性名字
        const char *name = property_getName(property);
        
        [array addObject:[NSString stringWithUTF8String:name]];
    }
    
    // 释放资源
    free(properties);
    
    return array;
}

/**
 *  插入一条数据
 */
- (BOOL)insertObjectToDatabaseWithObject:(id)object andFormName:(NSString *)formName
{
    if (![_database open])
    {
        // 数据库打开失败了
        return NO;
    }
    
    // 如果表不存在就创建
    if (![self isExistTableInDatabaseWithTableName:formName])
    {
        // 创建表
        if (![self createTableWithObject:[object class] andFormName:formName])
        {
            // 如果表创建失败，直接返回NO
            return NO;
        }
    }
    
    // 插入数据
    NSMutableString *sql = [NSMutableString stringWithFormat:@"insert into %@(",formName];
    
    // values (xx)
    NSMutableString *valueString = [NSMutableString stringWithString:@"values("];
    
    // 获取所有的属性
    NSArray *properties = [self propertiesFromClass:[object class]];
    
    // 遍历属性
    for (int i = 0; i < properties.count; i++)
    {
        if (i == properties.count - 1)
        {
            [sql appendFormat:@"%@)",properties[i]];
            [valueString appendFormat:@"'%@')",[object valueForKey:properties[i]]];
        }
        else
        {
            [sql appendFormat:@"%@,",properties[i]];
            [valueString appendFormat:@"'%@',",[object valueForKey:properties[i]]];
        }
    }
    
    [sql appendString:valueString];
    
    BOOL isFinish = [_database executeUpdate:sql];
    
    // 关闭数据库
    [_database close];
    
    return isFinish;
    
}

/**
 *  修改数据
 */
- (BOOL)updateObjectToDatabaseWithFormName:(NSString *)formName whitchColumnChangeName:(NSString *)changeColumnName theColumnChangeToValue:(NSString *)changeToValue whereColumnName:(NSString *)whereColumnName andWhereColumnValue:(NSString *)whereColumnValue
{
    if (![_database open])
    {
        return NO;
    }
    
    // 修改数据
    NSString *upDateStr = [NSString stringWithFormat:@"UPDATE %@ SET %@ = '%@' WHERE %@ = '%@';", formName, changeColumnName, changeToValue, whereColumnName, whereColumnValue];
    
    BOOL success = [_database executeUpdate:upDateStr];
    
    // 关闭数据库
    [_database close];
    
    return success;
}

/**
 *  删除一条数据
 */
- (BOOL)deleteObjectFromDatabaseWithFormName:(NSString *)formName whereColumnName:(NSString *)whereColumnName andWhereColumnValue:(NSString *)whereColumnValue
{
    if (![_database open])
    {
        return NO;
    }
    
    // 删除数据
    NSString *deleteStr = [NSString stringWithFormat:@"delete from %@ where %@ = '%@';", formName, whereColumnName, whereColumnValue];
    
    BOOL success = [_database executeUpdate:deleteStr];
    
    //关闭数据库
    [_database close];
    
    return success;
}

/**
 *  删除一条数据（根据多个字段）
 */
- (BOOL)deleteObjectWithFormName:(NSString *)formName whereColumnName:(NSArray *)whereColumnNameA andWhereColumnValue:(NSArray *)whereColumnValueA
{
    if (![_database open])
    {
        return NO;
    }

    // 删除数据
    NSString *deleteStr = [NSString stringWithFormat:@"delete from %@ where ", formName];
    for (int i = 0; i < whereColumnNameA.count; i++) {
        NSString *cNametr = whereColumnNameA[i];
        NSString *cValuetr = whereColumnValueA[i];
        if (i == 0) {
            deleteStr = [NSString stringWithFormat:@"%@%@ = '%@'", deleteStr, cNametr, cValuetr];
        } else {
            deleteStr = [NSString stringWithFormat:@"%@ AND %@ = '%@';", deleteStr, cNametr, cValuetr];
        }
    }

    BOOL success = [_database executeUpdate:deleteStr];
    
    // 关闭数据库
    [_database close];
    
    return success;
}

/**
 *  删除最早添加的数据
 */
- (BOOL)deleteTheEarliestDataWithFormName:(NSString *)formName
{
    if (![_database open])
    {
        return NO;
    }
    
    NSString *deleteStr = [NSString stringWithFormat:@"delete from %@ where myId = (select min(myId) from %@);", formName, formName];
//    NSString *deleteStr = [NSString stringWithFormat:@"DELETE FROM %@ WHERE myId IN(SELECT myId FROM %@ ORDER BY myId DESC LIMIT 0,1);", formName, formName];
    
    BOOL success = [_database executeUpdate:deleteStr];
    
    // 关闭数据库
    [_database close];
    return success;
}

/**
 *  删除最早添加的数据(根据某个字段)
 */
- (BOOL)deleteTheEarliestDataWithFormName:(NSString *)formName whereColumnName:(NSString *)whereColumnName andWhereColumnValue:(NSString *)whereColumnValue
{
    if (![_database open])
    {
        return NO;
    }
    
    NSString *deleteStr = [NSString stringWithFormat:@"delete from %@ where myId = (select min(myId) from %@) AND %@ = '%@';", formName, formName, whereColumnName, whereColumnValue];
    BOOL success = [_database executeUpdate:deleteStr];
    
    // 关闭数据库
    [_database close];
    return success;
}

/**
 *  查询表中数据数量
 */
- (NSInteger)queryFormCountFromDatabaseWithFormName:(NSString *)formName
{
    if (![_database open])
    {
        return 0;
    }
    
    NSString *sql = [NSString stringWithFormat:@"SELECT COUNT(*) FROM %@", formName];
    FMResultSet *s = [_database executeQuery:sql];
    NSInteger totalCount = 0;
    if ([s next]) {
        totalCount = [s intForColumnIndex:0];
    }
    return totalCount;
}

/**
 *  查询所有的数据
 */
- (NSArray *)queryAllObjectsFromDatabaseWithClass:(Class)cls andFormName:(NSString *)formName
{
    if (![_database open])
    {
        return nil;
    }
    
    // 保存数据模型
    NSMutableArray *objects = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"select * from %@", formName];
    
    // 执行查询
    FMResultSet *results = [_database executeQuery:sql];
    
    // 获取属性
    NSArray *properties = [self propertiesFromClass:cls];
    
    while (results.next)
    {
        id obj = [[cls alloc] init];
        
        // 遍历所有属性通过KVC赋值
        for (NSString *property in properties)
        {
            [obj setValue:[results stringForColumn:property] forKey:property];
        }
        
        // 添加到数组里面
        [objects addObject:obj];
    }
    
    // 关闭数据库
    [_database close];
    
    return objects;
}

/**
 *  查询出符合条件的所有数据
 */
- (NSArray *)queryObjectsFromDatabaseWithClass:(Class)cls formName:(NSString *)formName columnName:(NSString *)columnName andColumnValue:(NSString *)columnValue
{
    if (![_database open])
    {
        return nil;
    }
    
    // 保存数据模型
    NSMutableArray *objects = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"select * from %@ where %@ = '%@';", formName, columnName, columnValue];
    
    // 执行查询
    FMResultSet *results = [_database executeQuery:sql];
    
    // 获取属性
    NSArray *properties = [self propertiesFromClass:cls];
    
    while (results.next)
    {
        id obj = [[cls alloc] init];
        
        // 遍历所有属性通过KVC赋值
        for (NSString *property in properties)
        {
            [obj setValue:[results stringForColumn:property] forKey:property];
        }
        
        // 添加到数组里面
        [objects addObject:obj];
    }
    
    // 关闭数据库
    [_database close];
    
    return objects;
}

/**
 *  删除指定表的数据
 */
- (BOOL)deleteAllObjectsFromDatabaseWithFormName:(NSString *)formName
{
    if (![_database open])
    {
        return NO;
    }
    
    NSString *sql = [NSString stringWithFormat:@"delete from %@", formName];
    
    // 执行sql
    BOOL isOK = [_database executeUpdate:sql];
    // 关闭数据库
    [_database close];
    
    return isOK;
}

/**
 *  判断指定表是否存在
 *
 *  @param tablename 表名
 *  @return yes：存在     no：不存在
 */
- (BOOL)isExistTableInDatabaseWithTableName:(NSString *)tablename
{
    // sqlite_master是系统表
    NSString *sql = @"select name from sqlite_master where type='table' and name=?";
    // 查询
    FMResultSet *results = [_database executeQuery:sql, tablename];
    
    return results.next;
    
}
@end
